<?php
class Controller_DataCenter_Finance extends Controller_Abstract
{
	function actionSalesDataForFinance()
	{
		$this->_view['title'] = '销售数据-财务';
		$shop = Shop_User_Mapping::getTreeShopByPlatform();
		$this->_view['shop_group'] = $shop;
		if (! empty($_GET['is_search']))
		{
			set_time_limit(0);
			$shop_where = '';
			if ( ! empty($_GET['shop_id']))
			{
				if (is_array($_GET['shop_id']))
				{
					$shop_where .= ' and sales_order.shop_id in (' . implode(',', $_GET['shop_id']) . ')';
				}
				else
				{
					$shop_where .= ' and sales_order.shop_id=\'' . $_GET['shop_id'] . '\'';
				}
			}
			$sales_order = Sales_Order::find('sales_order.sales_time>=\'' . $_GET['begin'] . '\' and sales_order.sales_time<=\'' . $_GET['end'] . ' 23:59:59' . '\'' . $shop_where)
				->joinLeft('shop', '', 'sales_order.shop_id=shop.id')
				->joinLeft('currency', '' , 'sales_order.currency_id=currency.id')
				->joinLeft('warehouse', '', 'sales_order.warehouse_id=warehouse.id')
				->order('sales_order.shipped_time asc')
				->setColumns('shop.name as shop_name, sales_order.id, sales_order.number, sales_order.currency_rate, sales_order.amount, sales_order.sales_time, sales_order.shipped_time, sales_order.paid_time, sales_order.payment_method, sales_order.send_method, sales_order.shop_order_number, currency.code as currency_code, warehouse.name as warehouse_name')
				->asArray()->getAll();
			$data = mb_convert_encoding("网站/店铺,店铺单号,销售日期,收款日期,发货日期,订单号,产品数量,币种,汇率,销售额,成本,利润,利润率,收款方式,发货类型,仓库\n", 'CP936', 'UTF-8');
			$cost = 0;
			foreach ($sales_order as $so)
			{
				$sum = QDB::getConn()->execute('select sum(quantity) as quantity, sum(cost_price*quantity) as cost from sales_order_item where sales_order_item.sales_order_id=' . $so['id'])->fetchRow();
				$data .= $so['shop_name'] . ',';
				$data .= $so['shop_order_number'] . ','; 
				$data .= date('Y-m-d', strtotime($so['sales_time'])) . ',';
				$data .= date('Y-m-d', strtotime($so['paid_time'])) . ',';
				$data .= date('Y-m-d', strtotime($so['shipped_time'])) . ',';
				$data .= $so['number'] . ',';
				$data .= $sum['quantity'] . ',';
				$data .= $so['currency_code'] . ',';
				$data .= $so['currency_rate'] . ',';
				$data .= sprintf('%.2f',$so['amount']) . ',';
				$data .= sprintf('%.2f',$sum['cost']) . ',';
				$cost += $sum['cost'];
				$data .= sprintf('%.2f',$so['amount'] * $so['currency_rate'] - $sum['cost']) . ',';
				$data .= (($so['amount'] * $so['currency_rate']) ? sprintf('%.2f',($so['amount'] * $so['currency_rate'] - $sum['cost'])/($so['amount'] * $so['currency_rate']) * 100) . '%' : '-') . ',';
				$data .= mb_convert_encoding(Helper_BSS_Normal::getIniOne('sales_order_payment_method', 'value', $so['payment_method'], 'name'), 'CP936', 'UTF-8') . ',';
				$data .= mb_convert_encoding(Helper_BSS_Normal::getIniOne('sales_order_send_method', 'value', $so['send_method'], 'name'), 'CP936', 'UTF-8') . ',';
				$data .= mb_convert_encoding($so['warehouse_name'], 'CP936', 'UTF-8') . ',';
				$data .= "\n";
			}
			$file_name = 'sales_data_for_finance-' . date('Y-m-d', CURRENT_TIMESTAMP) . '-' . CURRENT_USER_NAME .".csv";
			file_put_contents(Q::ini('custom_system/export_dir') . $file_name, $data);
			$this->_view['file_name'] = Q::ini('custom_system/export_url') . $file_name;
		}
	}
	
	function actionProfitAnalysis()
	{
		$this->_view['title'] = '利润分析表';
		$product_categories = Product_Categories::getCategoriesTree();
		$this->_view['product_categories'] = json_encode($product_categories);
		$this->_view['shop_group'] = Shop_User_Mapping::getTreeShopByPlatform();
		if ( ! empty($_GET['is_search']) && ! empty($_GET['product_categories_id']) && ! empty($_GET['shop_id']))
		{
			$condition = Helper_BSS_Normal::buildCondition();
			$result = OLM_Report::profitAnalysis($condition);
			$this->_view['result'] = $result;
		}
	}
	
	function actionSalesStatisticsByPlatform()
	{
		$this->_view['title'] = '销售统计表-按平台';
		$is_search = isset($_GET['is_search']) ? $_GET['is_search'] : '';
		if ($is_search)
		{
			$url_arr = Helper_BSS_Normal::buildCondition();
			$result = OLM_Report::salesStatisticsByPlatform($url_arr);
			if ($result['ack'] == SUCCESS)
			{
				$this->_view['result'] = $result['result'];
			}
		}
	}
	
	function actionSalesStatisticsByAliExpressPlatform()
	{
		$this->_view['title'] = '销售统计表-速卖通';
		$shop = Shop_User_Mapping::getShopByPlatform('aliexpress');
		$this->_view['shop'] = $shop;
		$is_search = isset($_GET['is_search']) ? $_GET['is_search'] : '';
		if ($is_search)
		{
			$url_arr = Helper_BSS_Normal::buildCondition();
			if (empty($url_arr['shop_id'])) 
			{
				foreach ($shop as $s)
				{
					$url_arr['shop_id'][] = $s['id'];
				}
			}
			$result = OLM_Report::salesStatisticsByPlatform($url_arr);
			if ($result['ack'] == SUCCESS)
			{
				$this->_view['result'] = $result['result'];
			}
		}
	}
	
	function actionAliExpressLoanInfo()
	{
		$this->_view['title'] = '速卖通放款信息';
		$shop_status_enabled = Q::ini('custom_flag/shop_status/enabled/value');
		$aliexpress_shop = Shop::find('platform=? and status=?', 'aliexpress', $shop_status_enabled)->asArray()->getAll();
		$this->_view['aliexpress_shop'] = $aliexpress_shop;
		if (isset($_GET['is_search']) && $_GET['is_search'])
		{
			$cp = isset($_GET['cp']) ? $_GET['cp'] : 1;
			$ps = isset($_GET['ps']) ? $_GET['ps'] : 10;
			$url_arr = Helper_BSS_Normal::buildCondition();
			$url_arr['trash_status'] = Q::ini('custom_flag/sales_order_trash_status/normal/value');
			if (empty($_GET['shop_id']))
			{
				foreach ($aliexpress_shop as $key => $as)
				$url_arr['shop_id'][$key] = $as['id'];
			}
			$result = Sales_Order::Search($url_arr, $cp, $ps);
			if ($result['ack'] == SUCCESS)
			{
				$this->_view['sales_order'] = $result['data'];
				$page = new Helper_BSS_Page($result['pagination'], url('Datacenter::Finance/AliExpressLoanInfo', $url_arr));
				$this->_view['page'] = $page->getPage();
				$where = Sales_Order::buildSearchWhere($url_arr);
				$order_amount = QDB::getConn()->execute('select sum(sales_order.amount) as amount, sum(sales_order.amount*sales_order.currency_rate) as amount_cny, currency.code from sales_order left join currency on currency.id=sales_order.currency_id where ' . $where . ' group by sales_order.currency_id')->fetchAll();
				$loan_amount = QDB::getConn()->execute('select sum(sales_order.loan_amount) as loan_amount,loan_currency_code as code from sales_order where ' . $where . ' and loan_currency_code is not null group by loan_currency_code')->fetchAll();
				$this->_view['url_arr'] = $url_arr;
				$this->_view['order_amount'] = $order_amount;
				$this->_view['loan_amount'] = $loan_amount;
			}
		}
	}
	
	function actionExportAliExpressLoanInfo()
	{
		$this->_view['title'] = '下载速卖通放款信息';
		set_time_limit(300);
		$url_arr = Helper_BSS_Normal::buildcondition();
		if ($_GET['is_search'])
		{
			$result = Sales_Order::Search($url_arr, 0, 0);
			if ($result['ack'] == SUCCESS)
			{
				include Q::ini('custom_system/lib_dir') . 'PHPExcel.php';
				$phpexcel = new PHPExcel();
				$phpexcel->getProperties()->setCreator(Q::ini('custom_system/system_name') . Q::ini('custom_system/version'));
				$phpexcel->getProperties()->setLastModifiedBy(Q::ini('custom_system/system_name') . Q::ini('custom_system/version'));
				$phpexcel->getProperties()->setTitle('速卖通放款信息表');
				$phpexcel->getProperties()->setSubject('速卖通放款信息表');
				$phpexcel->getProperties()->setDescription('速卖通放款信息表-运营系统支撑系统');
				$phpexcel->getProperties()->setKeywords('速卖通放款信息表');
				$phpexcel->getProperties()->setCategory('速卖通放款信息表');
				$phpexcel->setActiveSheetIndex(0);
				$phpexcel->getActiveSheet()->setTitle('速卖通放款信息表');
				$phpexcel->getActiveSheet()->setCellValue('A1', '订单号');
				$phpexcel->getActiveSheet()->setCellValue('B1', '店铺单号');
				$phpexcel->getActiveSheet()->setCellValue('C1', '销售时间');
				$phpexcel->getActiveSheet()->setCellValue('D1', '发货时间');
				$phpexcel->getActiveSheet()->setCellValue('E1', '付款时间');
				$phpexcel->getActiveSheet()->setCellValue('F1', '币种');
				$phpexcel->getActiveSheet()->setCellValue('G1', '汇率');
				$phpexcel->getActiveSheet()->setCellValue('H1', '订单金额');
				$phpexcel->getActiveSheet()->setCellValue('I1', '放款币种代号');
				$phpexcel->getActiveSheet()->setCellValue('J1', '放款金额');
				$phpexcel->getActiveSheet()->setCellValue('K1', '放款时间');
				$i = 2;
				foreach ($result['data'] as $d)
				{
					$phpexcel->getActiveSheet()->setCellValue('A'.$i, $d['number']);
					$phpexcel->getActiveSheet()->getCell('B'.$i)->setValueExplicit($d['shop_order_number'], PHPExcel_Cell_DataType::TYPE_STRING);
					$phpexcel->getActiveSheet()->setCellValue('C'.$i, $d['sales_time']);
					$phpexcel->getActiveSheet()->setCellValue('D'.$i, $d['shipped_time']);
					$phpexcel->getActiveSheet()->setCellValue('E'.$i, $d['paid_time']);
					$phpexcel->getActiveSheet()->setCellValue('F'.$i, $d['currency_code']);
					$phpexcel->getActiveSheet()->setCellValue('G'.$i, $d['currency_rate']);
					$phpexcel->getActiveSheet()->setCellValue('H'.$i, $d['amount']);
					$phpexcel->getActiveSheet()->setCellValue('I'.$i, $d['loan_currency_code']);
					$phpexcel->getActiveSheet()->setCellValue('J'.$i, $d['loan_amount']);
					$phpexcel->getActiveSheet()->setCellValue('K'.$i, $d['loan_time']);
					++$i;
				}
				$outputFileName="AliExpressLoanInfo.xls";
				$objWriter = new PHPExcel_Writer_Excel5($phpexcel);
				header("Content-Type: application/vnd.ms-excel");
				header("Content-type: application/octet-stream");
				header("Content-type: application/download");
				header('Content-Disposition:inline;filename="' . $outputFileName . '"');
				header("Content-Transfer-Encoding:binary");
				header("Cache-Control: must-revalidate, post-check=0, pri-check=0");
				header("Pragma: no-cache");
				$objWriter->save('php://output');
				exit;
			}
		}
	}
	
	function actionShippingFee()
	{
		$this->_view['title'] = '邮寄费用报表';
		$is_search = isset($_GET['is_search']) ? $_GET['is_search'] : '';
		if ($is_search)
		{
			$url_arr = Helper_BSS_Normal::buildCondition();
			$result = OLM_Report::shippingFee($url_arr);
			if ($result['ack'] == SUCCESS)
			{
				$this->_view['result'] = $result['result'];
			}
		}
	}
	
	
	function actionAliShippingFee()
	{
		$this->_view['title'] = '邮寄费用报表-速卖通';
		$is_search = isset($_GET['is_search']) ? $_GET['is_search'] : '';
		$shop = Shop_User_Mapping::getShopByPlatform('aliexpress');
		$this->_view['shop'] = $shop;
		if ($is_search)
		{
			$url_arr = Helper_BSS_Normal::buildCondition();
			if (empty($url_arr['shop_id']))
			{
				foreach ($shop as $s)
				{
					$url_arr['shop_id'][] = $s['id'];
				}
			}
			$result = OLM_Report::shippingFee($url_arr);
			if ($result['ack'] == SUCCESS)
			{
				$this->_view['result'] = $result['result'];
			}
		}
	}
}